package dao.impl;

import dao.IAirportDAO;
import dao.IDAO;
import dao.tro.Airport;
import dao.tro.Country;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HSQLDBAirportDAO extends IDAO implements IAirportDAO{
    private Connection connection=null;
    private static final Logger Logs = Logger.getLogger(HSQLDBAirportDAO.class);

    public HSQLDBAirportDAO() {
        connection = HSQLDBDAOFactory.getConnection();
    }
    public void insert(Airport airport) {
        try {
            //подтягиваем ид страны по имени
            String query = "SELECT Id FROM Countries WHERE Name=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, airport.getInCountry().getName());
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long countryId = resultSet.getLong("Id");
            //вставляем аэропорт
            query = "INSERT INTO Airports VALUES (?, ?, ?, ?)";
            statement = connection.prepareStatement(query);
            statement.setLong(1, airport.getId());
            statement.setString(2, airport.getName());
            statement.setLong(3, countryId);
            statement.setInt(4, airport.getGatesNumber());
            statement.executeUpdate(query);
        } catch (SQLException e) {
            Logs.info("Ошибка вставки " + e);
        }
    }
    public void update(Airport airport) {
        try {
            //подтягиваем ид страны по имени
            String query = "SELECT Id FROM Countries WHERE Name=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, airport.getInCountry().getName());
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long countryId = resultSet.getLong("Id");
            //вставляем аэропорт
            query = "UPDATE Airports SET Airports.Name = ?, InCountry = ?, GatesNumber = ? WHERE id = ?";
            statement = connection.prepareStatement(query);
            statement.setLong(4, airport.getId());
            statement.setString(1, airport.getName());
            statement.setLong(2, countryId);
            statement.setInt(3, airport.getGatesNumber());
            statement.executeUpdate(query);
        } catch (SQLException e) {
            Logs.info("Ошибка изменения " + e);
        }
    }
    public void delete (long id) {
        super.delete(id);
    }
    public Airport showInformation(Long id) {
        Airport airport = new Airport();
        try {
            String query = "SELECT Countries.Name, Countries.Id FROM Airports INNER JOIN Countries ON Airports.InCountry=Countries.Id WHERE Airports.Id=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setLong(1, id);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long countryId = resultSet.getLong("Id");
            String countryName = resultSet.getString("Name");
            query = "SELECT Airports.Id, Airports.Name, Airports.GatesNumber FROM Airports WHERE Id=?";
            statement = connection.prepareStatement(query);
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            while(resultSet.next()) {
                airport.setId(id);
                airport.setGatesNumber(resultSet.getInt("GatesNumber"));
                Country country = new Country();
                country.setId(countryId);
                country.setName(countryName);
                airport.setInCountry(country);
                airport.setName(resultSet.getString("Name"));
            }
        } catch(SQLException e) {
            Logs.info("Ошибка выборки " + e);
        }
        return airport;
    }

    public Airport loadCountryName(Airport airport) {
        String query = "SELECT Countries.Name FROM Countries INNER JOIN Airports ON Airports.InCountry=Countries.id WHERE Airports.id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setLong(1, airport.getId());
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Country country = new Country();
            country.setName(resultSet.getString("Name"));
            airport.setInCountry(country);
        } catch (SQLException e) {
            Logs.info("Ошибка подгрузки имени страны " + e);
        }
        return airport;
    }

    @Override
    public List<Airport> allAirportList() {
        List<Airport> airportList = new ArrayList<Airport>();
        try {
            String query = "SELECT Id, Name FROM Airports";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();
            int listIterator = 0;
            while (resultSet.next()) {
                airportList.add(new Airport());
                airportList.get(listIterator).setName(resultSet.getString("Name"));
                airportList.get(listIterator).setId(resultSet.getLong("Id"));
                listIterator++;
            }
        } catch (SQLException e) {
            Logs.info("Ошибка загрузки списка аэропортов");
        }
        return airportList;
    }

    @Override
    public List<Airport> airportList(int airportCount) {
        List<Airport> airportList = new ArrayList<>();
        String query = "SELECT Airports.id, Airports.Name, Airports.GatesNumber, Countries.Name AS CName " +
                 "FROM Airports INNER JOIN Countries ON Airports.InCountry=Countries.Id";
        try {
             PreparedStatement statement = connection.prepareStatement(query);
             ResultSet resultSet = statement.executeQuery();
             int listIterator = 0;
             while((resultSet.next())&&(listIterator<airportCount)) {
                 airportList.add(new Airport());
                 airportList.get(listIterator).setName(resultSet.getString("Name"));
                 airportList.get(listIterator).setId(resultSet.getLong("Id"));
                 airportList.get(listIterator).setGatesNumber(resultSet.getInt("GatesNumber"));
                 airportList.get(listIterator).setInCountry(new Country());
                 airportList.get(listIterator).getInCountry().setName(resultSet.getString("CName"));
                 listIterator++;
             }
        } catch (SQLException e) {
             Logs.info("Ошибка загрузки списка аэропортов (полный список)");
        }
        return airportList;
    }

    public String getTableName() {
        return "Airports";
    }
}
